import pandas as pd

# 查询语句
"""
SELECT a.id 标签关系记录的id, 
  a.`content_id` ,
  a.`tag_field_id`,  
  b.id 标签id, 
  b.`parent_id` 标签父id,
  b.`tag_value`, 
  b.`tag_type`, 
  b.`tag_value` ,  
  b.`tag_name`  
  FROM  `t_course_tag_relation` a 
  inner join `t_course_tag`  b on a.`tag_id`  = b.`parent_id`  and a.`tag_field_id`  = b.`tag_value` 
  where 
  a.`content_id`  in (20238
)  and a.`content_type` =1 and a.`deleted` ='0'  and a.`tag_id` =755155151576178705 and b.`tag_value` in (1,2) and  b.`deleted` ='0'
"""
def merge_and_query_excels(file_paths, excel2_path, tag_name='复习课'):
    try:
        excel1 = pd.read_excel(file_paths, dtype={
                '标签关系记录的id': str,
                'content_id': str,
                '标签id': str,
                '标签父id': str
            })

        # 2. 读取excel2
        excel2 = pd.read_excel(excel2_path, dtype={'resource_id': str})

        # 确保必要列存在
        if 'content_id' not in excel1.columns or 'tag_name' not in excel1.columns or '标签关系记录的id' not in excel1.columns:
            raise ValueError("Required columns missing in merged excel1")
        if 'resource_id' not in excel2.columns:
            raise ValueError("Column 'resource_id' missing in excel2")

        # 3. 查询并处理结果
        results = []
        no_match_ids = []
        wrong_match_ids = []
        wrong_match_lessons = []
        multi_match_ids = []

        for resource_id in excel2['resource_id']:
            # 查询excel1中content_id匹配resource_id的行
            matches = excel1[excel1['content_id'] == resource_id]

            if len(matches) == 0:
                # 没有匹配数据，记录content_id
                no_match_ids.append(resource_id)
            elif len(matches) == 1:
                # 只有一行匹配，检查tag_name是否为“复习课”
                if matches.iloc[0]['tag_name'] != tag_name:
                    wrong_match_ids.extend(matches['标签关系记录的id'].tolist())
                    wrong_match_lessons.extend(matches['content_id'].tolist())
            else:
                # 多行匹配，记录标签关系记录的id
                multi_match_ids.extend(matches['标签关系记录的id'].tolist())


        # 4. 打印结果
        # print("查询结果：")
        # for result in results:
        #     print(result)

        if no_match_ids:
            print("\n没有匹配的content_id：")
            for id in no_match_ids:
                print(id)

        if multi_match_ids:
            print("\n多行匹配的标签关系记录的id：")
            for id in multi_match_ids:
                print(id)

        if wrong_match_ids:
            print("\n单行匹配错的的标签关系记录的id：")
            for id in wrong_match_ids:
                print(id)

        if wrong_match_lessons:
            print("\n单行匹配错的的标签关系记录的lesson_id：")
            for id in wrong_match_lessons:
                print(id)

        return excel1, results, no_match_ids, multi_match_ids

    except Exception as e:
        print(f"Error: {str(e)}")
        return None, None, None, None


# 使用示例
excel2_path = 'D:\\project_file\\20250918课程包标签处理\\复习课.xlsx'  # excel2文件路径
fuxi_excel = 'D:\\project_file\\20250918课程包标签处理\\复习课现存full.xlsx'  # 合并后的excel1输出路径

# 复习课
excel1, results, no_match_ids, multi_match_ids = merge_and_query_excels(fuxi_excel, excel2_path)

excel2_xinshou_path = 'D:\\project_file\\20250918课程包标签处理\\同步新授课.xlsx'  # excel2文件路径
xinshou_excel = 'D:\\project_file\\20250918课程包标签处理\\同步新授课现有数据\\同步新授课现有数据.xlsx'  # 合并后的excel1输出路径

# 同步新授课
# excel1, results, no_match_ids, multi_match_ids = merge_and_query_excels(xinshou_excel, excel2_xinshou_path, tag_name='同步新授课')